
truncate table asm_DW_CONTRATTO_ST1; 
insert into asm_DW_CONTRATTO_ST1 
 select         DISTINCT                                                       so.sog_id,
                                                                                                                                                                 so.sog_ragsoc,
                                                                                                                                                                 so.sog_pariva ,so.sog_codfis ,
                                                                                                                                                                 vc.con_anno,
                                                                                                                                                                 vc.con_id,
                                                                                                                                                                 vcuf.cuf_id,
                                                                                                                                                                 VC.TCON_ID,
                                                                                                                                                         
                                                                                                                                                                 vc.ver_dtini,
                                                                                                                                                                 vc.ver_dtfine,
                                                                                                                                                                 VCUF.SITO_ID,
                                                                                                                                                                 IND.ELETOPO_DES,
                                                                                                                                                                 IND.IND_NUM,
                                                                                                                                                                 IND.COM_DES,

                                                                                                                                                                  v_cufot.ver_num,
                                                                                                                                                                  V1.GF_ID 
                                                                                                                                                                       
                                                                                                                                                        from  contratto vc,soggetto so,
                                                                                                                                                            -- v_paredi,v_catasto,
                                                                                                                                                             v_cufot,
                                                                                                                                                                 sito v,conubifRN   vcuf,v_indirizzo ind,
                                                                                                                                                                 RAGFATGRUFAT v1,RAGFATCUF V2
                                                                                                                                                               where vc.az_id = '01'
                                                                                                                                                                            and so.sog_id=vc.sog_id
                                                                                                                                                                            and so.az_id = '01'
                                                                                                                                                                            and vc.con_anno = vcuf.con_anno
                                                                                                                                                                            and vc.con_id = vcuf.con_id
                                                                                                                                                                            and vc.az_id = vcuf.az_id
                                                                                                                                                                            and IND.az_id = '01'
                                                                                                                                                                            and V.az_id = '01'
                                                                                                                                                                            and so.az_id = '01'
                                                                                                                                                                            and VCUF.az_id = '01'
                                                                                                                                                                            AND VCUF.SITO_ID=V.SITO_ID
                                                                                                                                                                            AND V.IND_ID=IND.IND_ID
                                                                                                                                                                            
                         and                                                                                                                                                    
                        (SELECT CASE
                     WHEN ista.iss_staid <> 0
                     AND ista.iss_staidsuper = 0
                     AND ista.iss_sstaidsuper = 0
                        THEN ista.iss_staid
                     WHEN ista.iss_staid <> 0
                     AND ista.iss_staidsuper <> 0
                     AND ista.iss_sstaidsuper = 0
                        THEN ista.iss_staidsuper
                     WHEN ista.iss_staid <> 0
                     AND ista.iss_staidsuper <> 0
                     AND ista.iss_sstaidsuper <> 0
                        THEN ista.iss_sstaidsuper
                     ELSE -1
                  END
             FROM ista ista
            WHERE ista.az_id =vc.az_id
              AND ista.csi_id = vc.csi_id
              AND ista.ista_verstato =vc.ista_verstato)=2
                                                                                                                                                                            
                                                                                                                                                                            AND VC.VER_DTFINE = TO_DATE('31/12/9999','DD/MM/YYYY' )

                                                                                                                                                                               AND v_cufot.sta_idsys (+)= 2
                                                                                                                                                                               AND v_cufot.ver_dtfine =  TO_DATE ('31/12/9999', 'DD/MM/YYYY')
                                                                                                                                                                               and v_cufot.ot_id in (1,2)
                                                                                                                                                                               and v_cufot.cuf_id=vcuf.cuf_id
                                                                                                                                                                               AND VCUF.VER_DTFINE = TO_DATE('31/12/9999','DD/MM/YYYY' )
AND V.VER_DTFINE = TO_DATE('31/12/9999','DD/MM/YYYY' )
    AND              (SELECT CASE
                             WHEN ista.iss_staid <> 0
                             AND ista.iss_staidsuper = 0
                             AND ista.iss_sstaidsuper = 0
                                THEN ista.iss_staid
                             WHEN ista.iss_staid <> 0
                             AND ista.iss_staidsuper <> 0
                             AND ista.iss_sstaidsuper = 0
                                THEN ista.iss_staidsuper
                             WHEN ista.iss_staid <> 0
                             AND ista.iss_staidsuper <> 0
                             AND ista.iss_sstaidsuper <> 0
                                THEN ista.iss_sstaidsuper
                             ELSE -1
                          END
                     FROM ista ista
                    WHERE ista.az_id = V.az_id
                      AND ista.csi_id = V.csi_id
                      AND ista.ista_verstato = V.ista_verstato) =2
                           AND            (SELECT CASE
                             WHEN ista.iss_staid <> 0
                             AND ista.iss_staidsuper = 0
                             AND ista.iss_sstaidsuper = 0
                                THEN ista.iss_staid
                             WHEN ista.iss_staid <> 0
                             AND ista.iss_staidsuper <> 0
                             AND ista.iss_sstaidsuper = 0
                                THEN ista.iss_staidsuper
                             WHEN ista.iss_staid <> 0
                             AND ista.iss_staidsuper <> 0
                             AND ista.iss_sstaidsuper <> 0
                                THEN ista.iss_sstaidsuper
                             ELSE -1
                          END
                     FROM ista ista
                    WHERE ista.az_id = VCUF.az_id
                      AND ista.csi_id = VCUF.csi_id
                      AND ista.ista_verstato = VCUF.ista_verstato) =2
                                                                                                                                                                               --and vcuf.cuf_id=12848
                                                                                                                         and                 v1.az_id = '01' 
                                                                                                                             and v2.cuf_id = vcuf.cuf_id
                                                                                                                             AND V1.RAGF_ID = V2.RAGF_ID
                                                                                                                                         AND V1.VER_DTFINE = TO_DATE('31/12/9999','DD/MM/YYYY' )
                                                                                                                             AND V2.VER_DTFINE = TO_DATE('31/12/9999','DD/MM/YYYY' )
                                                                                                                             and          (SELECT CASE
                                                                                                                                 WHEN ista.iss_staid <> 0
                                                                                                                                 AND ista.iss_staidsuper = 0
                                                                                                                                 AND ista.iss_sstaidsuper = 0
                                                                                                                                    THEN ista.iss_staid
                                                                                                                                 WHEN ista.iss_staid <> 0
                                                                                                                                 AND ista.iss_staidsuper <> 0
                                                                                                                                 AND ista.iss_sstaidsuper = 0
                                                                                                                                    THEN ista.iss_staidsuper
                                                                                                                                 WHEN ista.iss_staid <> 0
                                                                                                                                 AND ista.iss_staidsuper <> 0
                                                                                                                                 AND ista.iss_sstaidsuper <> 0
                                                                                                                                    THEN ista.iss_sstaidsuper
                                                                                                                                 ELSE -1
                                                                                                                              END
                                                                                                                         FROM ista ista
                                                                                                                        WHERE ista.az_id = v1.az_id
                                                                                                                          AND ista.csi_id = v1.csi_id
                                                                                                                          AND ista.ista_verstato = v1.ista_verstato)= 2
                                                                                                                                           and          (SELECT CASE
                                                                                                                                 WHEN ista.iss_staid <> 0
                                                                                                                                 AND ista.iss_staidsuper = 0
                                                                                                                                 AND ista.iss_sstaidsuper = 0
                                                                                                                                    THEN ista.iss_staid
                                                                                                                                 WHEN ista.iss_staid <> 0
                                                                                                                                 AND ista.iss_staidsuper <> 0
                                                                                                                                 AND ista.iss_sstaidsuper = 0
                                                                                                                                    THEN ista.iss_staidsuper
                                                                                                                                 WHEN ista.iss_staid <> 0
                                                                                                                                 AND ista.iss_staidsuper <> 0
                                                                                                                                 AND ista.iss_sstaidsuper <> 0
                                                                                                                                    THEN ista.iss_sstaidsuper
                                                                                                                                 ELSE -1
                                                                                                                              END
                                                                                                                         FROM ista ista
                                                                                                                        WHERE ista.az_id = v2.az_id
                                                                                                                          AND ista.csi_id = v2.csi_id
                                                                                                                          AND ista.ista_verstato = v2.ista_verstato)= 2                                       ;